var END_COMMAND = "\r\n";
var SIGN_IN = 'signin';
var SIGN_UP = 'signup';
var CHANGE_PASSWORD = 'changepassword';
var UPDATE_PROFILE = 'updateprofile';
var SEARCH_FRIENDS = 'searchfriends';
var ADD_FRIEND = 'addfriend';
var ACCEPT_FRIEND_REQUEST = 'acceptfriendrequest';
var NOT_ACCEPT_FRIEND_REQUEST = 'notacceptfriendrequest';
var DELETE_FRIEND = 'deletefriend';
var POST_LAST_STATUS = 'postlaststatus';
var CHAT = '!@#$%chat!@#$%';
var SET_HAVE_READ_MESSAGE = 'sethavereadmessage';
var ADD_TO_GROUP = 'addtogroup';
var HAVE_NOT_GROUP = 'havenotgroup';
var HAVE_GROUP = 'havegroup';
var STATUS = "$$$";

//-------------init
var net = require("net");
var mysql = require('mysql');
var clients = [];
var dictionaryConnections = {}; //luu all stream cua all user ket noi vao
var groupchat = [];
var groupchats = new Array(); //{};
//var client = null;//new Client();

//--------------connect to database
var connection = mysql.createConnection({
	database : 'livejs',
	host : 'localhost',
	user : 'root',
	password : '',
});
//create Server
var server = net.createServer(function (stream) {
	var client = new Client(stream);
	clients.push(client);
    
	stream.addListener("connect", function () {
		console.log('>>> A new client has connected!');
		//socket.setTimeout(10 * 60 * 1000); 
		stream.setTimeout(5000); 
	});
	
	stream.addListener("data", function (data) {
		console.log(data.toString());
		var command = [];
		command = data.toString().split("##");
		if(command[0] == SIGN_IN) { //-------sign in-----------
			var str = [];
			str = command[1].split("!!");
			//checkUserSignIn(str[0], str[1]);
			clients.forEach(function(c) {
				if(c==client) {
					connection.query("SELECT * FROM users WHERE email = '"+str[0]+"'", function(err, rows) {
						if (err) throw err;
						if(rows.length == 1) {
							if(rows[0].password === str[1]) {
								// load list friend in DB for client
								connection.query("SELECT * FROM users WHERE id IN (SELECT id_friend FROM list_friends INNER JOIN users ON users.id = list_friends.id_user WHERE users.id = '"+rows[0].id+"' AND list_friends.is_active = 1)", function(err, rsFriend) {
									if (err) throw err;
									// load friend request in DB for client
									connection.query("SELECT * FROM users WHERE id IN (SELECT id_friend FROM list_friends INNER JOIN users ON users.id = list_friends.id_user WHERE users.id = '"+rows[0].id+"' AND list_friends.is_active = 0 AND list_friends.is_requester = 0)", function(err, rsFriendRequest) {
										if (err) throw err;
										// load message offline in DB for client
										connection.query("SELECT users.id, users.full_name, users.email, users.avatar, message_offline.msg, message_offline.send_date FROM users INNER JOIN message_offline ON users.id = message_offline.id_user WHERE message_offline.id_friend = '"+rows[0].id+"' AND is_read = 0", function(err, rsMsg) {
											if (err) throw err;
											//write to client
											dictionaryConnections[rows[0].id] = c.stream;
											c.stream.write('RES_SIGNED_IN##'+JSON.stringify(rows)+"##"+JSON.stringify(rsFriend)+"##"+JSON.stringify(rsFriendRequest)+"##"+JSON.stringify(rsMsg));
											c.stream.write(END_COMMAND);
										});
									});
									if(rows[0].status != str[2]) {
										connection.query('UPDATE users SET status = ? WHERE email = ?', [str[2], str[0]], function(err, result) {
											if (err) throw err;
											if(result.affectedRows == 1) {
												console.log('changed status');
											}
										});
									}
								});
								client.id = rows[0].id;
								client.full_name = rows[0].full_name;
								client.email = rows[0].email;
								client.password = rows[0].password;
								client.gender = rows[0].gender;
								client.birthday = rows[0].birthday;
								client.status = rows[0].status;
								client.last_status = rows[0].last_status;
								
							} else {
								console.log('Wrong password! Pls try again!');
								c.stream.write('RES_WRONG_PASSWORD##');
								c.stream.end();
							}
						} else if(rows.length == 0) {
							console.log('Account is not exist!');
							c.stream.write('RES_NOT_EXIST_ACCOUNT##');
							c.stream.end();
						} else {
							c.stream.write('ERR_SOS');
							c.stream.end();
							throw 'ERR_SOS';
						}
					});
				}
			});
		} else if(command[0] == SIGN_UP) { //---------sign up---------
			var userSignUp = JSON.parse(command[1]);
			clients.forEach(function(c) {
				if(c==client) {
					connection.query("SELECT * FROM users WHERE email = '"+userSignUp.email+"'", function(err, rows, fields) {
						if (err) throw err;
						if(rows.length == 1) {
							stream.write('RES_EXISTED_ACCOUNT');
							stream.end();
						} else if(rows.length == 0) {
							//insert data
							var values = [userSignUp.full_name, userSignUp.email, userSignUp.password, userSignUp.birthday, userSignUp.gender];
							connection.query("INSERT INTO `users` (full_name, email, password, birthday, gender) VALUES (?, ?, ?, ?, ?);", values, function(err, insertNum) {
								if(err) {
									throw err;
								}
								if(insertNum.affectedRows == 1) {
									console.log('Signed up successfully!');
									c.stream.write('RES_SIGNED_UP');
									c.stream.end();
								} else {
									console.log('Sign up fail!');
									c.stream.write('RES_SIGN_UP_FAIL');
									c.stream.end();
								}
							});
						} else {
							c.stream.write('ERR_SOS');
							c.stream.end();
							throw 'ERR_SOS';
						}
					});
				}
			});
		}  else if(command[0] == CHANGE_PASSWORD) { //---------change password---------
			var str = [];
			str = command[1].split("!!");
			clients.forEach(function(c) {
				if(c==client && c.email == str[0]) {
					connection.query('UPDATE users SET password = ? WHERE email = ?', [str[1], str[0]], function(err, result) {
						if (err) throw err;
						if(result.affectedRows == 1) {
							console.log('Changed your password successfully!');
							c.stream.write('RES_CHANGED_PASSWORD_SUCCESS');
							c.stream.write(END_COMMAND);
						} else if(result.affectedRows == 0) {
							console.log('Change password fail!');
							c.stream.write('RES_CHANGE_PASSWORD_FAIL');
							c.stream.write(END_COMMAND);
						} else {
							c.stream.write('ERR_SOS');
							c.stream.write(END_COMMAND);
							throw 'ERR_SOS';
						}
					});
				}
			});
		}  else if(command[0] == UPDATE_PROFILE) { //---------change password---------
			clients.forEach(function(c) {
				if(c==client) {
					var newProfile = JSON.parse(command[1]);
					var values = [newProfile.full_name, newProfile.birthday, newProfile.gender, newProfile.email];
					connection.query("UPDATE `users` SET full_name = ?, birthday = ?, gender = ? WHERE email = ?;", values, function(err, numUpdate){
						if (err) throw err;
						if(numUpdate.affectedRows == 1) {
							c.stream.write('RES_UPDATED_PROFILE_SUCCESS');
							c.stream.write(END_COMMAND);
						} else if(numUpdate.affectedRows == 0) {
							c.stream.write('RES_UPDATE_PROFILE_FAIL');
							c.stream.write(END_COMMAND);
						} else {
							c.stream.write('ERR_SOS');
							c.stream.write(END_COMMAND);
							throw 'ERR_SOS';
						}
					});
				}
			});
		} else if(command[0] == SEARCH_FRIENDS) { //---------search friends---------
			var str = [];
			str = command[1].split("!!");
			clients.forEach(function(c) {
				if(c==client) {
					connection.query("SELECT id, email, full_name, birthday, gender, last_status, status FROM users WHERE email LIKE '%"+str[1]+"%' OR full_name LIKE '%"+str[1]+"%'", function(err, rows, fields) {
						if (err) throw err;
						if(rows.length != 0) {
							c.stream.write('RES_HAVE_RESULTS##');
							c.stream.write(JSON.stringify(rows));
							c.stream.write(END_COMMAND);
						} else {
							c.stream.write('RES_NO_RESULT##');
							c.stream.write(END_COMMAND);
						}
					});
				}
			});
		} else if(command[0] == ADD_FRIEND) { //---------add friend---------
			var str = [];
			str = command[1].split("!!");
			var values = [str[0], str[1]];
			clients.forEach(function(c) {
				if(c==client) {
					connection.query("SELECT * FROM list_friends WHERE id_friend = '"+str[1]+"' AND id_user = '"+str[0]+"'", function(err, rows, fields) {
						if (err) throw err;
						if(rows.length != 0) {
							if(rows[0].is_active == 0) {
								c.stream.write('RES_HAS_SEND_FRIEND_REQUEST');
								c.stream.write(END_COMMAND);
							} else {
								c.stream.write('RES_EXISTED_FRIEND');
								c.stream.write(END_COMMAND);
							}
						} else {
							// insert friend data into my record
							connection.query("INSERT INTO `list_friends` (id_user, id_friend, is_requester) VALUES (?, ?, 1);", values, function(err, insertNum) {
								if(err) {
									throw err;
								}
								if(insertNum.affectedRows == 1) {
									console.log('Added friend successfully!');
									c.stream.write('RES_ADDED_FRIEND');
									c.stream.write(END_COMMAND);
									// send response to friend_id if this user is online
									if(typeof dictionaryConnections[str[1]] !== 'undefined') {
										//get user_id's request to send to friend_id
										connection.query("SELECT * FROM users WHERE id = '"+str[0]+"'", function(err, usr) {
											if (err) throw err;
											if(usr.length == 1) {
												console.log('Sended friend request!');
												dictionaryConnections[str[1]].write('RES_NOTIFY_FRIEND_REQUEST##'+JSON.stringify(usr));
												dictionaryConnections[str[1]].write(END_COMMAND);
											}
										});
									}
									// insert my data into friend's record
									connection.query("INSERT INTO `list_friends` (id_friend, id_user) VALUES (?, ?);", values, function(err, num) {
										if(err) {
											throw err;
										}
									});
								} else {
									console.log('Add friend fail!');
									c.stream.write('RES_ADD_FRIEND_FAIL');
									c.stream.write(END_COMMAND);
								}
							});
						}
					});
				}
			});
		} else if(command[0] == DELETE_FRIEND) { //---------delete friend---------
			var str = [];
			str = command[1].split("!!");
			clients.forEach(function(c) {
				if(c==client) {
					connection.query("DELETE FROM list_friends WHERE id_friend = '"+str[1]+"' AND id_user = '"+str[0]+"'", function(err, deleteNum) {
						if (err) throw err;
						if(deleteNum.affectedRows == 1) {
							console.log('Deleted friend successfully!');
							c.stream.write('RES_DELETED_FRIEND');
							c.stream.write(END_COMMAND);
						} else {
							console.log('Delete friend fail!');
							c.stream.write('RES_DELETE_FRIEND_FAIL');
							c.stream.write(END_COMMAND);
						}
					});
				}
			});
		} else if(command[0] == ACCEPT_FRIEND_REQUEST) { //---------accept friend---------
			var str = [];
			str = command[1].split("!!");
			clients.forEach(function(c) {
				if(c==client) {
					connection.query('UPDATE list_friends SET is_active = 1 WHERE id_user = ? AND id_friend = ?', [str[0], str[1]], function(err, result1) {
						if (err) throw err;
						if(result1.affectedRows == 1) {
							connection.query('UPDATE list_friends SET is_active = 1 WHERE id_user = ? AND id_friend = ?', [str[1], str[0]], function(err, result2) {
								if (err) throw err;
								if(result2.affectedRows == 1) {
									console.log('Accept friend request successfully!');
									c.stream.write('RES_ACCEPT_FRIEND_REQUEST_SUCCESS');
									c.stream.write(END_COMMAND);
									if(typeof dictionaryConnections[str[1]] !== 'undefined') {
										//get user_id's request to send to friend_id
										connection.query("SELECT * FROM users WHERE id = '"+str[0]+"'", function(err, usr) {
											if (err) throw err;
											if(usr.length == 1) {
												console.log('Send friend response!');
												dictionaryConnections[str[1]].write('RES_FRIEND_RESPONSE##'+JSON.stringify(usr));
												dictionaryConnections[str[1]].write(END_COMMAND);
											}
										});
									}
								}
							});
						} else if(result1.affectedRows == 0) {
							console.log('Accept friend request fail!');
							c.stream.write('RES_ACCEPT_FRIEND_REQUEST_FAIL');
							c.stream.write(END_COMMAND);
						} else {
							c.stream.write('ERR_SOS');
							c.stream.write(END_COMMAND);
							throw 'ERR_SOS';
						}
					});
				}
			});
		} else if(command[0] == NOT_ACCEPT_FRIEND_REQUEST) { //---------do not accept friend---------
			var str = [];
			str = command[1].split("!!");
			clients.forEach(function(c) {
				if(c==client) {
					connection.query("DELETE FROM list_friends WHERE id_friend = '"+str[1]+"' AND id_user = '"+str[0]+"'", function(err, deleteNum1) {
						if (err) throw err;
					});
					connection.query("DELETE FROM list_friends WHERE id_friend = '"+str[0]+"' AND id_user = '"+str[1]+"'", function(err, deleteNum2) {
						if (err) throw err;
					});
				}
			});
		} else if(command[0] == POST_LAST_STATUS) { //---------post last_status---------
			var str = [];
			str = command[1].split("!!");
			clients.forEach(function(c) {
				var values = [str[1], str[0]];
				connection.query("UPDATE `users` SET last_status = ? WHERE id = ?;", values, function(err, numUpdate){
					if (err) throw err;
					if(numUpdate.affectedRows == 1) {
						c.stream.write('RES_POSTED_LAST_STATUS');
						c.stream.write(END_COMMAND);
					} else if(numUpdate.affectedRows == 0) {
						c.stream.write('RES_POST_LAST_STATUS_FAIL');
						c.stream.write(END_COMMAND);
					} else {
						console.log('ERR_SOS');
						c.stream.write('ERR_SOS');
						c.stream.write(END_COMMAND);
						throw 'ERR_SOS';
					}
				});
			});
		} else if(command[0] == CHAT) { //---------chat---------
			var str = [];
			str = command[1].split("!!");
			// id A!!name A!!id Group!!text!!date time
			//gui cho all user in group
			groupchats[str[2]].forEach(function(item) {
				//neu khac nguoi send tn thi moi gui tn
				if(str[0] != item) {
					if(typeof dictionaryConnections[item] !== 'undefined') {
						// write: id A!!name A!!text
						dictionaryConnections[item].write('CHAT##'+str[2]+"##"+str[1]+"##"+str[3]);
						dictionaryConnections[item].write(END_COMMAND);
					} else {
						// thuc hien luu msg vao DB
						// insert: id A!!id B!!text!!date time
						var values = [str[0], item, str[3], str[4]];
						connection.query("INSERT INTO `message_offline` (id_user, id_friend, msg, send_date) VALUES (?, ?, ?, ?);", values, function(err, num) {
							if(err) {
								throw err;
							}
							if(num.affectedRows == 0) {
								dictionaryConnections[str[0]].write('RES_SEND_MSG_FAIL');
								dictionaryConnections[str[0]].write(END_COMMAND);
							}
						});
					}
				}
			});
			
			/*if(typeof dictionaryConnections[str[2]] !== 'undefined') {
				// write: id A!!name A!!text
				dictionaryConnections[str[2]].write('CHAT##'+str[0]+"##"+str[1]+"##"+str[3]);
				dictionaryConnections[str[2]].write(END_COMMAND);
			} else {
				// thuc hien luu msg vao DB
				// insert: id A!!id B!!text!!date time
				var values = [str[0], str[2], str[3], str[4]];
				connection.query("INSERT INTO `message_offline` (id_user, id_friend, msg, send_date) VALUES (?, ?, ?, ?);", values, function(err, num) {
					if(err) {
						throw err;
					}
					if(num.affectedRows == 0) {
						dictionaryConnections[str[0]].write('RES_SEND_MSG_FAIL');
						dictionaryConnections[str[0]].write(END_COMMAND);
					}
				});
			}*/
		} else if(command[0] == SET_HAVE_READ_MESSAGE) { //---------set have read message---------
			var str = [];
			str = command[1].split("!!");
			var values = [str[1], str[0]];
			connection.query("UPDATE message_offline SET is_read = 1 WHERE id_user = ? AND id_friend = ?", values, function(err, numUpdate){
				if (err) throw err;
				if(numUpdate.affectedRows != 0) {
					console.log('Have read message offline!');
					dictionaryConnections[str[0]].write('RES_HAVE_READ_MESSAGE');
					dictionaryConnections[str[0]].write(END_COMMAND);
				}
			});
		} else if(command[0] == ADD_TO_GROUP) { //---------add friend to group chat---------
			var str = [];
			str = command[1].split("!!");
			//var values = [str[1], str[0]];
			if(str[0] == HAVE_NOT_GROUP) { // create new group (chat betwen 2 user)
				var time = new Date();
				var idGrp = str[1]+""+time.getHours()+""+time.getMinutes()+""+time.getSeconds();
				console.log(idGrp);
				groupchats[idGrp] = new Array();
				groupchats[idGrp][groupchats[idGrp].length] = str[1];
				groupchats[idGrp][groupchats[idGrp].length] = str[2];
				// write idGrp to client
				dictionaryConnections[str[1]].write('RES_GROUP_ID##' + idGrp);
				dictionaryConnections[str[1]].write(END_COMMAND);
				
				groupchats[idGrp].forEach(function(item){
					console.log(item);
				});
			} else if(str[0] == HAVE_GROUP) { // ----------add user into existed group-----------
				// idGrp!!idUserForAdd
				groupchats[str[1]][groupchats[str[1]].length] = str[2];
				console.log(groupchats[str[1]]);
			}
		}
	});

	stream.addListener("end", function() {
		clients.splice(clients.indexOf(client), 1);
		clients.forEach(function(c) {
			console.log(">>> " +client.email+ " has left!");
			//c.stream.write(STATUS+client.email + " has left!\r\n");
			c.stream.end();
		});
		stream.end();
		//connection.end();
	});
});

//------------------
function Client(stream) {
	this.full_name = null;
	this.email = null;
	this.password = null;
	this.gender = null;
	this.status = null;
	this.last_status = null;
	this.birthday = null;
	this.avatar = null;
	this.buffer = "";
	this.stream = stream;
}

function checkUserSignIn(email, password) {
	//connection.connect();
	connection.query("SELECT * FROM users WHERE email = '"+email+"' AND password = '"+password+"'", function(err, rows, fields) {
		if (err) throw err;
		if(rows.length !== 0) {
			//console.log('Query result: ', rows);
			client = new Client();
			client.full_name = rows[0].full_name;
			client.email = rows[0].email;
			client.password = rows[0].password;
			client.gender = rows[0].gender;
			client.birthday = rows[0].birthday;
			console.log('birthday = ' + client.birthday);
			//stream.write(client);
		}
	});
	//connection.end();
}

//------------------
server.listen(9123);
console.log("==============================================="); 
console.log("|| Vo Thai Vu - Server for LiveJS application ||");
console.log("||           Do An Tot nghiep 2013            ||");
console.log("===============================================");
console.log("Waitting on 9123...\r\n");